/* 
  funciones para busqueda de comprobantes electronicos

  ale - 23/03/2021
*/

CREATE OR REPLACE FUNCTION public.getcommelectronicoemitido(
    integer,
    integer,
    integer,
    character varying,
    character varying,
    character varying)
  RETURNS integer AS
$BODY$
-- la funcion recupera el comm con igual (pafipcbtetipo, pnroptovta, pcodcai) emitido por la empresa.
-- La condicion de "emitido" es por ptipocai = 'W'
-- La condicion "por la empresa" es por la sucursal del comprobante en lista de sucursales de la empresa.
DECLARE
pafipcbtetipo ALIAS FOR $1;        
pnroptovta    ALIAS FOR $2;        
pnro          ALIAS FOR $3;        
ptipocai      ALIAS FOR $4;        
pcodcai       ALIAS FOR $5;
pSysEmpSuc    ALIAS FOR $6;    
BEGIN
  RETURN (SELECT id
          FROM comm
          WHERE comm.afipcbtetipo = pafipcbtetipo AND
                comm.nroptovta = pnroptovta AND
                comm.nro = pnro AND
                pSysEmpSuc LIKE '%' || comm.codambsuc || '%' AND
                comm.tipocai = ptipocai AND
                comm.codcai = pcodcai);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.getcommelectronicoemitido(integer, integer, integer, character varying, character varying, character varying)
  OWNER TO abacouser;
GRANT EXECUTE ON FUNCTION public.getcommelectronicoemitido(integer, integer, integer, character varying, character varying, character varying) TO public;
GRANT EXECUTE ON FUNCTION public.getcommelectronicoemitido(integer, integer, integer, character varying, character varying, character varying) TO abacouser;


-- ----------------------------------------------------------------

CREATE OR REPLACE FUNCTION public.getfechacommcaependienteprevio(
    integer,
    integer,
    character varying,
    timestamp without time zone,
    character varying,
    character varying)
  RETURNS timestamp AS
$BODY$
-- la funcion recupera fecha de comprobante pendiente de CAE anterior a una fecha para un (pafipcbtetipo, pnroptovta) emitido por la empresa.
-- La condicion de "emitido" es por ptipocai = 'W'
-- La condicion "por la empresa" es por la sucursal del comprobante en lista de sucursales de la empresa.
-- Tiene en cuenta que el comprobante no este anulado
DECLARE
pafipcbtetipo ALIAS FOR $1;        
pnroptovta    ALIAS FOR $2;        
pStrAnulado   ALIAS FOR $3;        
pFecha        ALIAS FOR $4;
ptipocai      ALIAS FOR $5;        
pSysEmpSuc    ALIAS FOR $6;    
BEGIN
  RETURN (SELECT CAST (MIN(fecha) AS TIMESTAMP)
	  FROM comm
	  WHERE comm.afipcbtetipo = pafipcbtetipo AND
                comm.nroptovta = pNroptovta AND
                comm.detalle <> pStrAnulado AND
                pSysEmpSuc LIKE '%' || comm.codambsuc || '%' AND
                comm.fecha < pFecha AND
                comm.tipocai = pTipoCai AND
	        comm.codcai IS NULL);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION public.getfechacommcaependienteprevio(integer, integer, character varying,  timestamp without time zone, character varying, character varying)
  OWNER TO abacouser;
GRANT EXECUTE ON FUNCTION public.getfechacommcaependienteprevio(integer, integer, character varying,  timestamp without time zone, character varying, character varying) TO public;
GRANT EXECUTE ON FUNCTION public.getfechacommcaependienteprevio(integer, integer, character varying,  timestamp without time zone, character varying, character varying) TO abacouser;   


-- =================================
SELECT aaVersion('453.00','452.06');
-- =================================